![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Appendix B
|
PINS | The number of times the item in the library cache was executed. |
RELOADS | The number of times the library cache missed and the library object was reloaded. |
Cache Miss Rate = SUM(RELOADS)/SUM(PINS)
SQL Statements
Total Miss Percent:
SELECT SUM(reloads) "Cache Misses", SUM(pins) "Executions", 100 * ( SUM(reloads) / SUM(pins) ) "Cache Miss Percent" FROM v$librarycache;
Individual Statistics:
SELECT namespace, reloads "Cache Misses", pins "Executions" FROM v$librarycache;
Statistics for the data dictionary cache are stored in the dynamic performance table V$ROWCACHE (the data dictionary cache is sometimes known as the row cache). The important columns to view in this table are GETS and GETMISSES:
GETS | The total number of requests for the particular item. |
GETMISSES | The total number of requests resulting in cache misses. |
Cache Miss Rate = SUM(GETMISSES)/SUM(GETS)
SQL Statements
Total Miss Percent:
SELECT SUM(getmisses) "Cache Misses", SUM(gets) "Requests", 100 * ( SUM(getmisses) / SUM(gets) ) "Cache Miss Percent" FROM v$rowcache;
Individual Statistics:
SELECT parameter, getmisses "Cache Misses", gets "Requests" FROM v$rowcache;
The statistics for the buffer cache are kept in the dynamic performance table V$SYSSTAT. The important columns to view in this table are PHYSICAL READS, DB BLOCK GETS, and CONSISTENT GETS:
PHYSICAL READS | The total number of requests that result in a disk access. This is a cache miss. |
DB BLOCK GETS | The number of requests for blocks in current mode. |
CONSISTENT GETS | The number of requests for blocks in consistent mode. A consistent-mode request is one that is satisfied from a rollback record for consistency. |
Cache Hit Ratio = 1 - PHYSICAL READS / ( DB BLOCK GETS + CONSISTENT GETS )
SQL Statement
Block Buffer Information:
SELECT name, value FROM v$sysstat WHERE name IN ('db block gets', 'consistent gets', 'physical reads');
Remember: DB Block Buffer Size = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE
Information about disk accesses is kept in the dynamic performance table V$FILESTAT. Important information in this table is found in the following columns:
PHYRDS | The number of physical reads done to the data file. |
PHYWRTS | The number of physical writes done to the data file. |
The information in V$FILESTAT is referenced by file number. The dynamic performance table V$DATAFILE contains a reference to this number as well as this useful information:
NAME | The name of the data file. |
STATUS | The type of file and its current status. |
BYTES | The size of the data file. |
SQL Statement
I/O Information:
SELECT SUBSTR(name,1,40), phyrds, phywrts, status, bytes FROM v$datafile df, v$filestat fs WHERE df.file# = fs.file#;
Remember that disks have certain inherent limitations that cannot be exceeded.
Disk I/O Review
Sequential I/O | Data is written to or read from the disk in order; very little head movement occurs. Access to the redo log files is always sequential. |
Random I/O | Data is accessed in different places on the disk; lots of head movement occurs. Access to the data files is almost always random. For database loads, access is sequential; in most other cases (especially OLTP), access patterns are almost always random. |
Disk I/O Rates
Remember these general limitations (and refer to Chapter 14, Advanced Disk I/O Concepts):
Sequential I/O | A typical SCSI-II disk drive can support approximately 100 to 150 sequential I/Os per second. |
Random I/O | A typical SCSI-II disk drive can support approximately 50 to 60 random I/Os per second. |
I/O Rules of Thumb
Isolate sequential I/Os | Because sequential I/Os can occur at a much higher rate, isolating them lets you run these drives much faster. |
Spread out random I/Os | You can accomplish this goal by striping table data using Oracle striping, OS striping, or hardware striping. |
Separate data and indexes | By separating a heavily used table from its index, you allow a query to a table to access data and indexes on separate disks simultaneously. |
Reduce non-Oracle I/O | Eliminate non-Oracle disk I/O from disks that contain database files. Any other disk I/O slows down Oracles access to these disks. |
You can check for chained rows with the ANALYZE commands LIST CHAINED ROWS option. Use these SQL statements to check for chained or migrated rows:
Rem Create the Chained Rows Table Rem CREATE TABLE chained_rows ( owner_name varchar2(30), table_name varchar2(30), cluster_name varchar2(30), head_rowid rowid, timestamp date); Rem Rem Analyze the Table in Question Rem ANALYZE TABLE scott.emp LIST CHAINED ROWS; Rem Rem Check the Results Rem SELECT * from chained_rows;
You can check the number of recursive calls through the dynamic performance table V$SYSSTAT. Use the following command:
SELECT name, value FROM v$SYSSTAT WHERE name = 'recursive calls';
You can tell whether you are seeing contention on rollback segments by looking at the dynamic performance table V$WAITSTAT. V$WAITSTAT contains the following data related to rollback segments:
UNDO HEADER | The number of waits for buffers containing rollback header blocks. |
UNDO BLOCK | The number of waits for buffers containing rollback blocks other than header blocks. |
SYSTEM UNDO HEADER | Same as UNDO HEADER for the SYSTEM rollback segment. |
SYSTEM UNDO BLOCK | Same as UNDO BLOCK for the SYSTEM rollback segment. |
SQL Statement
Rollback Information:
SELECT class, count FROM V$WAITSTAT WHERE class IN ('undo header', 'undo block', 'system undo header', 'system undo block');
To determine whether dynamic growth of rollback segments is a problem, look in the dynamic performance table V$ROLLSTAT. The following columns are of particular interest:
EXTENTS | Number of rollback extents. |
RSSIZE | The size in bytes of the rollback segment. |
OPTSIZE | The size that the OPTIMAL parameter was set to. |
AVEACTIVE | The current average size of active extents. Here active extents are extents with uncommitted transaction data. |
AVESHRINK | The total size of free extents divided by the number of shrinks. |
EXTENDS | The number of times the rollback segment added an extent. |
SHRINKS | The number of times the rollback segment shrank. This shrink may be one or more extents at a time. |
HWMSIZE | The high-water mark of rollback segment size. This is the largest that the segment ever grew to be. |
SQL Statement
Rollback Dynamic Growth Information:
SELECT substr(name, 1,40), extents, rssize, aveactive, aveshrink, extends, shrinks FROM v$rollname rn, v$rollstat rs WHERE rn.usn = rs.usn;
Information concerning redo log buffer contention is stored in the dynamic performance table V$SYSSTAT as the value of the redo log space requests entry. If this number is not zero, it means that a process had to wait for space in the redo log buffer; you should increase its size. Check for this condition with the following SQL statement.
SQL Statement
Redo Log Buffer Contention Information:
SELECT name, value FROM v$sysstat WHERE name = 'redo log space requests';
Latch contention can be determined by examining the dynamic performance table V$LATCH. The significant values are described here:
GETS | For willing-to-wait requests. The number of successful requests. |
MISSES | For willing-to-wait requests. The number of times the initial request failed. |
SLEEPS | For willing-to-wait requests. The number of times subsequent requests failed. |
IMMEDIATE_GETS | For immediate requests. The number of successful requests. |
IMMEDIATE_MISSES | For immediate requests. The number of times the request failed. |
SQL Statement
Redo Latch Contention Information:
SELECT SUBSTR(name,1,20), gets, misses, sleeps, immediate_gets, immediate_misses FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
One way to tell whether your sorts are occurring in memory or on disk is to look in the dynamic performance table V$SYSSTAT. The statistics of interest are give here:
sorts (memory) | The number of sorts that were able to fit in the in-memory sort area. |
sorts (disk) | The number of sorts that required temporary space on disk. |
SQL Statement
Sort Performance Information:
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
Contention on the free list can be determined by looking at the dynamic performance table V$WAITSTAT using the following SQL statement.
SQL Statement
Free List Contention Information:
SELECT class, count FROM v$waitstat WHERE class = 'free list';
Table of Contents |
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement. |